package com.wuliu.action.app;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import beartool.MD5;

import com.wuliu.action.BaseAction;
import com.wuliu.business.sys.RoleObj;
import com.wuliu.business.sys.UserAddrObj;
import com.wuliu.business.sys.UserBankObj;
import com.wuliu.business.sys.UserObj;
import com.wuliu.model.sys.Role;
import com.wuliu.model.sys.User;
import com.wuliu.model.sys.UserAddr;
import com.wuliu.model.sys.UserBank;
import com.wuliu.util.BaseUtil;
import com.wuliu.util.DbUtil;

public class AppSyncAction extends BaseAction {
	
	public String getDemoData(){

		JSONObject jsonRet = new JSONObject();
		JSONArray jsonList = new JSONArray();
		
		for(int i=0;i<10;i++){
			JSONObject json = new JSONObject();
			json.put("id", i);		//经销商编码
			json.put("name", "名字"+i);	//经销商名称

			jsonList.add(json);
		}
		
		jsonRet.put("Data", jsonList);
		
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String register(){
		String userAccount = BaseUtil.getStr(request.getParameter("userAccount"));
		String userName = BaseUtil.getStr(request.getParameter("userName"));

		String userPwd = BaseUtil.getStr(request.getParameter("userPwd"));
		String userTel = BaseUtil.getStr(request.getParameter("userTel"));
		
		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
		if(userAccount.equals("")||userPwd.equals("")){
			showMsg="账号或密码不能为空！";
		}else{
			User user = new User();
			user.setUserAccount(userAccount);
			user.setUserName(userName);
			user.setUserPwd(userPwd);
			user.setUserTel(userTel);
			
	    	if(BaseUtil.getStr(UserObj.getUserByAccount(user.getUserAccount()).getUserAccount()).equals("")){
	    		String sql	= "INSERT INTO WL_USER(USER_ID,USER_ACCOUNT,USER_PWD,USER_NAME," +
	    				"USER_DESC,USER_SPOT,USER_TEL,ACTIVE_FLAG,ADD_DATE,ADD_TIME,ADD_BY) " +
	    				" VALUES(?,?,?,?,?,?,?,?,DATE_FORMAT(NOW(),'%Y%m%d') ,DATE_FORMAT(NOW(),'%H%i%s'),?)";
	    		List<String> lt = new ArrayList<String>();
	    		lt.add(user.getUserAccount());
	    		lt.add(user.getUserAccount());
	    		MD5 md5 = new MD5();
	            lt.add(md5.getMD5ofStr(user.getUserPwd()));
	    		lt.add(user.getUserName());
	    		lt.add(user.getUserDesc());
	    		lt.add(user.getUserSpot());
	    		lt.add(user.getUserTel());
	    		lt.add(user.getActiveFlag());
	    		lt.add("app");
	    		boolean result	= DbUtil.excuSQL(lt, sql);
	    		if(result){
	    			showMsg="注册成功！";
	    		}else{
	    			showMsg="注册失败！";
	    		} 		
	    	}else{
	    		showMsg="该账号已存在！";
	    	}
		}
		
		
		
		jsonRet.put("showMsg", showMsg);
		
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	

	
	public String login(){
		String userAccount = request.getParameter("userAccount");
		String userPwd = request.getParameter("userPwd");
		User user = new User();
		user.setUserAccount(userAccount);
		user.setUserPwd(userPwd);
		List<Role> list = new ArrayList<Role>();
		String showMsg = "登陆成功！";
    	if(BaseUtil.getStr(UserObj.getUserByAccount(user.getUserAccount()).getUserAccount()).equals("")){
    		showMsg="账号不存在！";
    	}else{
    		MD5 md5 = new MD5();
    		if(!UserObj.getUserByAccount(user.getUserAccount()).getUserPwd().equals(md5.getMD5ofStr(user.getUserPwd()))){
    			showMsg="密码错误！";
    		}else{
    			user=UserObj.getUserByAccount(user.getUserAccount());
    			list=RoleObj.getRoleListByUser(user.getUserAccount());
    		}
    	}
    	
    	
		JSONObject jsonRet = new JSONObject();
		
		jsonRet.put("showMsg", showMsg);
		jsonRet.put("user", user);
		jsonRet.put("roleList", list);
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}

	public String editPwd(){
		String userAccount = BaseUtil.getStr(request.getParameter("userAccount"));

		String userPwd = BaseUtil.getStr(request.getParameter("userPwd"));
		
		User user = new User();
		user.setUserAccount(userAccount);
		user.setUserPwd(userPwd);
		
		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
		if(userAccount.equals("")||userPwd.equals("")){
			showMsg="账号或密码不能为空！";
		}else{

			
	    		String sql	= "UPDATE WL_USER SET USER_PWD=?,UPD_BY=?, " +
	    				" UPD_DATE=DATE_FORMAT(NOW(),'%Y%m%d'),UPD_TIME=DATE_FORMAT(NOW(),'%H%i%s') " +
	    				" WHERE USER_ACCOUNT=? ";
	    		List<String> lt = new ArrayList<String>();
	    		MD5 md5 = new MD5();
	            lt.add(md5.getMD5ofStr(user.getUserPwd()));
	    		lt.add("app");
	    		lt.add(user.getUserAccount());
	    		boolean result	= DbUtil.excuSQL(lt, sql);
	    		if(result){
	    			showMsg="修改成功！";
	    		}else{
	    			showMsg="修改失败！";
	    		} 		

		}
		
		jsonRet.put("showMsg", showMsg);
		jsonRet.put("user", UserObj.getUserByAccount(user.getUserAccount()));
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String editTel(){
		String userAccount = BaseUtil.getStr(request.getParameter("userAccount"));

		String userTel = BaseUtil.getStr(request.getParameter("userTel"));
		
		User user = new User();
		user.setUserAccount(userAccount);
		user.setUserTel(userTel);
		
		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
		if(userAccount.equals("")||userTel.equals("")){
			showMsg="账号或密码不能为空！";
		}else{
    		String sql	= "UPDATE WL_USER SET USER_TEL=?,UPD_BY=?, " +
    				" UPD_DATE=DATE_FORMAT(NOW(),'%Y%m%d'),UPD_TIME=DATE_FORMAT(NOW(),'%H%i%s') " +
    				" WHERE USER_ACCOUNT=? ";
    		List<String> lt = new ArrayList<String>();
            lt.add(user.getUserTel());
    		lt.add("app");
    		lt.add(user.getUserAccount());
    		boolean result	= DbUtil.excuSQL(lt, sql);
    		if(result){
    			showMsg="修改成功！";
    		}else{
    			showMsg="修改失败！";
    		} 		
		}
		jsonRet.put("showMsg", showMsg);
		jsonRet.put("user", UserObj.getUserByAccount(user.getUserAccount()));
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public String saveUserAddr(){
		String userAccount = BaseUtil.getStr(request.getParameter("userAccount"));
		String addrText = BaseUtil.getStr(request.getParameter("addrText"));
		String telNo = BaseUtil.getStr(request.getParameter("telNo"));
		String addrType = BaseUtil.getStr(request.getParameter("addrType"));
		String ownerName = BaseUtil.getStr(request.getParameter("ownerName"));

		UserAddr userAddr = new UserAddr();
		userAddr.setKeyId(UUID.randomUUID().toString());
		userAddr.setUserAccount(userAccount);
		userAddr.setAddrText(addrText);
		userAddr.setTelNo(telNo);
		userAddr.setAddrType(addrType);
		userAddr.setOwnerName(ownerName);

		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
		if(userAccount.equals("")){
			showMsg="账号不能为空！";
		}else{			
    		String sql	= "INSERT INTO WL_USER_ADDR(KEY_ID,USER_ACCOUNT,ADDR_TEXT, " +
    				" TEL_NO,ADDR_TYPE,OWNER_NAME) VALUES(?,?,?,?,?,?) ";
    		List<String> lt = new ArrayList<String>();
            lt.add(userAddr.getKeyId());
    		lt.add(userAddr.getUserAccount());
    		lt.add(userAddr.getAddrText());
    		lt.add(userAddr.getTelNo());
    		lt.add(userAddr.getAddrType());
    		lt.add(userAddr.getOwnerName());
	    		boolean result	= DbUtil.excuSQL(lt, sql);
	    		if(result){
	    			showMsg="新增成功！";
	    		}else{
	    			showMsg="新增失败！";

	    		} 		
		}
		
		jsonRet.put("showMsg", showMsg);
		jsonRet.put("userAddr", UserAddrObj.getUserAddrById(userAddr.getKeyId()));
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String getUserAddrList(){
		String userAccount = request.getParameter("userAccount");

		String showMsg = "";	
		JSONObject jsonRet = new JSONObject();
		JSONArray jsonArray = new JSONArray();
		if(userAccount.equals("")){
			showMsg="账号不能为空！";
			jsonRet.put("showMsg", showMsg);
		}else{
			String sql = "SELECT KEY_ID,USER_ACCOUNT,ADDR_TEXT,TEL_NO,ADDR_TYPE,OWNER_NAME " +
					" FROM WL_USER_ADDR WHERE USER_ACCOUNT='"+userAccount+"' ";
			List<List<String>> list = DbUtil.getQueryList(sql, 6);
			for(List<String> lt:list){
				JSONObject json = new JSONObject();
				json.put("keyId", lt.get(0));
				json.put("userAccount", lt.get(1));
				json.put("addrText", lt.get(2));
				json.put("telNo", lt.get(3));
				json.put("addrType", lt.get(4));
				json.put("ownerName", lt.get(5));
				jsonArray.add(json);
			}
			jsonRet.put("userAddrList", jsonArray);
		}
    	
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String editUserAddr(){
		String keyId = BaseUtil.getStr(request.getParameter("keyId"));

		String addrText = BaseUtil.getStr(request.getParameter("addrText"));
		String telNo = BaseUtil.getStr(request.getParameter("telNo"));
		String addrType = BaseUtil.getStr(request.getParameter("addrType"));
		String ownerName = BaseUtil.getStr(request.getParameter("ownerName"));
		
		UserAddr userAddr = new UserAddr();
		
		userAddr.setKeyId(keyId);
		userAddr.setAddrText(addrText);
		userAddr.setTelNo(telNo);
		userAddr.setAddrType(addrType);
		userAddr.setOwnerName(ownerName);
		
		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
		if(keyId.equals("")){
			showMsg="更新主键不能为空！";
		}else{
    		String sql	= "UPDATE WL_USER_ADDR SET ADDR_TEXT=?,TEL_NO=?,ADDR_TYPE=?," +
    				"OWNER_NAME=? WHERE KEY_ID=? ";
    		List<String> lt = new ArrayList<String>();
            lt.add(userAddr.getAddrText());
    		lt.add(userAddr.getTelNo());
    		lt.add(userAddr.getAddrType());
    		lt.add(userAddr.getOwnerName());
    		lt.add(userAddr.getKeyId());
    		boolean result	= DbUtil.excuSQL(lt, sql);
    		if(result){
    			showMsg="修改成功！";
    		}else{
    			showMsg="修改失败！";
    		} 		
		}
		jsonRet.put("showMsg", showMsg);
		jsonRet.put("userAddr", UserAddrObj.getUserAddrById(userAddr.getKeyId()));
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public String delUserAddr(){
		String keyId = BaseUtil.getStr(request.getParameter("keyId"));

		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
		if(keyId.equals("")){
			showMsg="更新主键不能为空！";
		}else{
    		String sql	= "DELETE FROM WL_USER_ADDR WHERE KEY_ID=? ";
    		List<String> lt = new ArrayList<String>();
            lt.add(keyId);

    		boolean result	= DbUtil.excuSQL(lt, sql);
    		if(result){
    			showMsg="删除成功！";
    		}else{
    			showMsg="删除失败！";
    		} 		
		}
		jsonRet.put("showMsg", showMsg);

		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public String getUserAddrById(){
		String keyId = request.getParameter("keyId");

		UserAddr userAddr = new UserAddr();
		userAddr.setKeyId(keyId);
		JSONObject jsonRet = new JSONObject();
		jsonRet.put("userAddr", UserAddrObj.getUserAddrById(userAddr.getKeyId()));
		
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	/**
	 * 银行卡管理
	 */
	public String saveUserBank(){
		String userAccount = BaseUtil.getStr(request.getParameter("userAccount"));
		String bankCode = BaseUtil.getStr(request.getParameter("bankCode"));
		String cardType = BaseUtil.getStr(request.getParameter("cardType"));
		String cardNo = BaseUtil.getStr(request.getParameter("cardNo"));
		String telNo = BaseUtil.getStr(request.getParameter("telNo"));
		String checkCode = BaseUtil.getStr(request.getParameter("checkCode"));
		String validDate = BaseUtil.getStr(request.getParameter("validDate"));
		
		UserBank userBank = new UserBank();
		userBank.setKeyId(UUID.randomUUID().toString());
		userBank.setUserAccount(userAccount);
		userBank.setBankCode(bankCode);
		userBank.setCardType(cardType);
		userBank.setCardNo(cardNo);
		userBank.setTelNo(telNo);
		
		userBank.setCheckCode(checkCode);
		userBank.setValidDate(validDate);
		
		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
		if(userAccount.equals("")){
			showMsg="账号不能为空！";
		}else{			
	    		String sql	= "INSERT INTO WL_USER_BANK(KEY_ID,USER_ACCOUNT,BANK_CODE, " +
	    				" CARD_TYPE,CARD_NO,TEL_NO,CHECK_CODE,VALID_DATE) VALUES(?,?,?,?,?,?,?,?) ";
	    		List<String> lt = new ArrayList<String>();
	            lt.add(userBank.getKeyId());
	    		lt.add(userBank.getUserAccount());
	    		lt.add(userBank.getBankCode());
	    		lt.add(userBank.getCardType());
	    		lt.add(userBank.getCardNo());
	    		lt.add(userBank.getTelNo());
	    		lt.add(userBank.getCheckCode());
	    		lt.add(userBank.getValidDate());
	    		boolean result	= DbUtil.excuSQL(lt, sql);
	    		if(result){
	    			showMsg="新增成功！";
	    		}else{
	    			showMsg="新增失败！";

	    		} 		
		}
		
		jsonRet.put("showMsg", showMsg);
		jsonRet.put("userBank", UserBankObj.getUserBankById(userBank.getKeyId()));
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String getUserBankList(){
		String userAccount = request.getParameter("userAccount");

		String showMsg = "";	
		JSONObject jsonRet = new JSONObject();
		JSONArray jsonArray = new JSONArray();
		if(userAccount.equals("")){
			showMsg="账号不能为空！";
			jsonRet.put("showMsg", showMsg);
		}else{
			String sql = "SELECT KEY_ID,USER_ACCOUNT,BANK_CODE,CARD_TYPE," +
					" CARD_NO,TEL_NO,CHECK_CODE,VALID_DATE FROM WL_USER_BANK " +
					" WHERE USER_ACCOUNT='"+userAccount+"' ";
			List<List<String>> list = DbUtil.getQueryList(sql, 8);
			for(List<String> lt:list){
				JSONObject json = new JSONObject();
				json.put("keyId", lt.get(0));
				json.put("userAccount", lt.get(1));
				json.put("bankCode", lt.get(2));
				json.put("cardType", lt.get(3));
				json.put("cardNo", lt.get(4));
				json.put("telNo", lt.get(5));
				json.put("checkCode", lt.get(6));
				json.put("validDate", lt.get(7));
				jsonArray.add(json);
			}
			jsonRet.put("userBankList", jsonArray);
		}
    	
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String editUserBank(){
		String keyId = BaseUtil.getStr(request.getParameter("keyId"));

		String bankCode = BaseUtil.getStr(request.getParameter("bankCode"));
		String cardType = BaseUtil.getStr(request.getParameter("cardType"));
		String cardNo = BaseUtil.getStr(request.getParameter("cardNo"));
		String telNo = BaseUtil.getStr(request.getParameter("telNo"));
		
		String checkCode = BaseUtil.getStr(request.getParameter("checkCode"));
		String validDate = BaseUtil.getStr(request.getParameter("validDate"));

		UserBank userBank = new UserBank();

		userBank.setKeyId(keyId);
		userBank.setBankCode(bankCode);
		userBank.setCardType(cardType);
		userBank.setCardNo(cardNo);
		userBank.setTelNo(telNo);
		
		userBank.setCheckCode(checkCode);
		userBank.setValidDate(validDate);
				
		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
		if(keyId.equals("")){
			showMsg="更新主键不能为空！";
		}else{
    		String sql	= "UPDATE WL_USER_BANK SET BANK_CODE=?,CARD_TYPE=?," +
    				" CARD_NO=?,TEL_NO=?,CHECK_CODE=?,VALID_DATE=? WHERE KEY_ID=? ";
    		List<String> lt = new ArrayList<String>();
            lt.add(userBank.getBankCode());
            lt.add(userBank.getCardType());
            lt.add(userBank.getCardNo());
    		lt.add(userBank.getTelNo());
    		lt.add(userBank.getKeyId());
    		lt.add(userBank.getCheckCode());
    		lt.add(userBank.getValidDate());
    		boolean result	= DbUtil.excuSQL(lt, sql);
    		if(result){
    			showMsg="修改成功！";
    		}else{
    			showMsg="修改失败！";
    		} 		
		}
		jsonRet.put("showMsg", showMsg);
		jsonRet.put("userAddr", UserBankObj.getUserBankById(userBank.getKeyId()));
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public String getOrderList(){
		String userAccount = request.getParameter("userAccount");
		String roleType = request.getParameter("roleType");//
		String fromDate = request.getParameter("fromDate");//'yyyy-mm-dd'
		String endDate = request.getParameter("endDate");
		String orderStatus = request.getParameter("orderStatus");
	
		JSONObject jsonRet1 = new JSONObject();
		JSONObject jsonRet = new JSONObject();
		JSONObject json_detail = new JSONObject();
		JSONArray jsonArray = new JSONArray();
		JSONArray jsonArray_detail = new JSONArray();
		JSONArray jsona=new JSONArray();
		
		String sql = "SELECT ID,SHIPPER_ID,SHIPPER_NAME,SHIPPER_TEL," +
				"CONSIGNEE_ID,CONSIGNEE_NAME,CONSIGNEE_TEL,CONSIGNEE_ADDRE," +
				"SENT_DATE,RECEIVE_DATE,CHECK_STATUS,ORDER_STATUS,COMPANY_ID," +
				"COMPANY_NAME,GOODS_NAME,FEE,TRANSFER_TYPE,PAY_TYPE," +
				"PAY_PEOPLE,FEE_ON_TRANSFER,TOTAL_FEE,IS_INSURE,IS_INSURANCE," +
				"CHECKER,CAR_ID,PAY_CODE,SHIPPER_ADDRE,PAY_FEE_TYPE,TOTAL_FEE_STATUS,SEND_TYPE FROM WL_ORDER ";
		
	
		if(roleType!=null&&roleType.equals("1")){
			sql+=" WHERE SHIPPER_ID='"+userAccount+"' ";
		}else if(roleType!=null&&roleType.equals("2")){
			sql+=" WHERE CONSIGNEE_ID='"+userAccount+"' ";
		}else if(roleType!=null&&roleType.equals("3")){
			sql+="WHERE (SHIPPER_ID='"+userAccount+"'OR CONSIGNEE_ID='"+userAccount+"') ";
		}
		if(orderStatus!=null&&!orderStatus.equals("")){
			sql+=" AND ORDER_STATUS='"+orderStatus+"' ";
		}
		if(fromDate!=null&&!fromDate.equals("")){
			sql+= " AND SENT_DATE >= " +
					" unix_timestamp('"+fromDate+" 00:00:00') ";
		}		
		if(endDate!=null&&!endDate.equals("")){
			sql+= " AND SENT_DATE <= " +
					" unix_timestamp('"+endDate+" 23:59:59')+1 ";
		}
		
		List<List<String>> list = DbUtil.getQueryList(sql, 30);
		for(List<String> lt:list){
			JSONObject json = new JSONObject();
			json.put("id", lt.get(0));
			json.put("shipperId", lt.get(1));
			json.put("shipperName", lt.get(2));
			json.put("shipperTel", lt.get(3));
			json.put("consigneeId", lt.get(4));
			json.put("consigneeName", lt.get(5));
			json.put("consigneeTel", lt.get(6));
			json.put("consigneeAddre", lt.get(7));
			json.put("sentDate", lt.get(8));
			json.put("receiveDate", lt.get(9));
			json.put("checkStatus", lt.get(10));
			json.put("orderStatus", lt.get(11));
			json.put("companyId", lt.get(12));
			json.put("companyName", lt.get(13));
			json.put("goodsName", lt.get(14));
			json.put("fee", lt.get(15));
			json.put("transferType", lt.get(16));
			json.put("payType", lt.get(17));
			json.put("payPeople", lt.get(18));
			json.put("feeOnTransfer", lt.get(19));
			json.put("totalFee", lt.get(20));
			json.put("isInsure", lt.get(21));
			json.put("isInsurance", lt.get(22));
			json.put("checker", lt.get(23));
			json.put("carId", lt.get(24));
			json.put("payCode", lt.get(25));
			json.put("shipperAddre", lt.get(26));
			json.put("payFeeType", lt.get(27));
			json.put("totalFeeStatus", lt.get(28));
			json.put("sendType", lt.get(29));
			jsonArray.add(json);
			jsonRet1.put("order", json);
			
			
			String sql_detail="SELECT ID,ORDER_ID,GOODS_NAME,GOODS_VOLUME," +
					"GOODS_NUM,GOODS_WEIGHT,GOODS_FEE FROM WL_ORDER_DETAIL WHERE ORDER_ID= '"+lt.get(0)+"' ";
			List<List<String>> list_detail = DbUtil.getQueryList(sql_detail, 7);
			for(List<String> lt_detail:list_detail){
				
				json_detail.put("id", lt_detail.get(0));
				json_detail.put("orderId", lt_detail.get(1));
				json_detail.put("goodsName", lt_detail.get(2));
				json_detail.put("goodsVolume", lt_detail.get(3));
				json_detail.put("goodsNum", lt_detail.get(4));
				json_detail.put("goodsWeight", lt_detail.get(5));
				json_detail.put("goodsFee", lt_detail.get(6));
				
				jsonArray_detail.add(json_detail);
				jsonRet1.put("orderDetail", jsonArray_detail);
			}
			//json.put("orderDetail", jsonArray_detail);
			//jsonArray.add(json);
			
			
			
			jsona.add(jsonRet1);
			
			jsonRet.put("orderList", jsona);
		}
//		jsonRet.put("orderList", jsonArray);
//		jsonRet.put("jsonArray_detail", jsonArray_detail);
		
		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}

	
	public String getUserList(){
	
		JSONObject jsonRet = new JSONObject();
		JSONArray jsonArray = new JSONArray();

		String sql = "SELECT USER_ID,USER_ACCOUNT,USER_PWD,USER_NAME,USER_DESC," +
				"USER_SPOT,USER_TEL,ACTIVE_FLAG FROM WL_USER ";
		
		List<List<String>> list = DbUtil.getQueryList(sql, 8);
		for(List<String> lt:list){
			JSONObject json = new JSONObject();
			json.put("userId", lt.get(0));
			json.put("userAccount", lt.get(1));
			json.put("userPwd", lt.get(2));
			json.put("userName", lt.get(3));
			json.put("userDesc", lt.get(4));
			json.put("userSpot", lt.get(5));
			json.put("userTel", lt.get(6));
			json.put("activeFlag", lt.get(7));

			jsonArray.add(json);
		}
		jsonRet.put("userList", jsonArray);

		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String getRoleList(){
		
		JSONObject jsonRet = new JSONObject();
		JSONArray jsonArray = new JSONArray();

		String sql = "SELECT ROLE_ID,ROLE_NAME,ROLE_DESC,ACTIVE_FLAG FROM WL_USER ";
		
		List<List<String>> list = DbUtil.getQueryList(sql, 4);
		for(List<String> lt:list){
			JSONObject json = new JSONObject();
			json.put("roleId", lt.get(0));
			json.put("roleName", lt.get(1));
			json.put("roleDesc", lt.get(2));
			json.put("activeFlag", lt.get(3));

			jsonArray.add(json);
		}
		jsonRet.put("roleList", jsonArray);

		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String getSpotList(){
		
		JSONObject jsonRet = new JSONObject();
		JSONArray jsonArray = new JSONArray();

		String sql = "SELECT SPOT_ID,SPOT_CODE,SPOT_NAME,PROVINCE,CITY,TOWN," +
				"SPOT_ADDR,SPOT_TEL,RIGHT_CODE FROM WL_USER ";
		
		List<List<String>> list = DbUtil.getQueryList(sql, 9);
		for(List<String> lt:list){
			JSONObject json = new JSONObject();
			json.put("spotId", lt.get(0));
			json.put("spotCode", lt.get(1));
			json.put("spotName", lt.get(2));
			json.put("province", lt.get(3));
			json.put("city", lt.get(4));
			json.put("town", lt.get(5));
			json.put("spotAddr", lt.get(6));
			json.put("spotTel", lt.get(7));
			json.put("rightCode", lt.get(8));

			jsonArray.add(json);
		}
		jsonRet.put("spotList", jsonArray);

		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String attachUserRole(){
		String userAccount = BaseUtil.getStr(request.getParameter("userAccount"));
		String roleId = BaseUtil.getStr(request.getParameter("roleId"));
		
		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
	
		String sql	= "INSERT INTO WL_USER_ROLE(UR_ID,USER_ACCOUNT,ROLE_ID, " +
				" ADD_BY,ADD_DATE,ADD_TIME) VALUES(?,?,?,?,DATE_FORMAT(NOW(),'%Y%m%d') ,DATE_FORMAT(NOW(),'%H%i%s')) ";
		List<String> lt = new ArrayList<String>();
        lt.add(UUID.randomUUID().toString());
		lt.add(userAccount);
		lt.add(roleId);
		lt.add("app");
		boolean result	= DbUtil.excuSQL(lt, sql);
		if(result){
			showMsg="新增成功！";
		}else{
			showMsg="新增失败！";
		} 		

		jsonRet.put("showMsg", showMsg);

		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	public String attachUserSpot(){
		String userAccount = BaseUtil.getStr(request.getParameter("userAccount"));
		String spotCode = BaseUtil.getStr(request.getParameter("spotCode"));
		
		JSONObject jsonRet = new JSONObject();
		String showMsg = "";
	
		String sql	= "UPDATE WL_USER SET USER_SPOT=? WHERE USER_ACCOUNT=? ";
		List<String> lt = new ArrayList<String>();
        lt.add(spotCode);
		lt.add(userAccount);
		boolean result	= DbUtil.excuSQL(lt, sql);
		if(result){
			showMsg="新增成功！";
		}else{
			showMsg="新增失败！";
		} 		

		jsonRet.put("showMsg", showMsg);

		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String getUserByUserAccount(){
		
		String userAccount = request.getParameter("userAccount");
		User user = UserObj.getUserByAccount(userAccount);
		JSONObject jsonRet = new JSONObject();
		jsonRet.put("user", user);

		try {
			response.setCharacterEncoding("utf8");
			response.getWriter().print(jsonRet);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
}
